Database consolidation involves distributing and sharing computing resources among multiple databases. Databases may be consolidated using a container database management system. A consolidated database, such as a multitenant container database (CDB), includes one or more pluggable databases (PDBs). The container database in which a pluggable database is established is referred to herein as the container database of the pluggable database.
A container database includes a data dictionary, which comprises metadata that defines database objects in the container database. For example, the data dictionary for a given CDB will include metadata describing each PDB that is contained in the given CDB, including database objects included in each PDB. In effect, a database dictionary defines the totality of a database. Database objects include tables, table columns, and tablespaces. A tablespace comprises a set of one or more files that are used to store the data for various types of database objects, such as tables, indexes, etc.
Further, each pluggable database includes a separate PDB-specific database dictionary that comprises metadata that defines database objects contained in the pluggable database. If a database object is stored in a tablespace of a particular PDB, the data dictionary of the particular PDB maps the database object to the tablespace that holds the data for the database object.
The database dictionary within a pluggable database also includes metadata that indicates a database character set based on which character data in the pluggable database is encoded. Herein, the character set based on which character data of a pluggable database is encoded is referred to herein as follows: the character set with which the pluggable database is encoded; or the character set of the pluggable database.
Pluggable databases may be “plugged in” to a container database, and may be transported between database servers and/or database management systems (DBMSs). The container database may manage multiple pluggable databases and a given database server instance may serve those pluggable databases from the container database. As such, a given container database allows multiple pluggable databases to run on the same database server and/or database server instance, allowing the computing resources of a single database server or instance to be shared between multiple pluggable databases. In a container database management system, each pluggable database may be opened or closed in the container database independently from other pluggable databases.
A user may access a pluggable database by establishing a database session on the container database management system for that pluggable database, where a database session represents the connection between the user and the container database management system for accessing the pluggable database. A database session is initiated for a pluggable database by, for example, transmitting a request for a new connection to the container database management system, the request specifying the pluggable database. A container database management system may host multiple database sessions, each database session being for one of multiple pluggable databases and/or for the root database of a container database.
Generally, before a pluggable database may be accessed through a container database management system, the pluggable database must be encoded using the same character set as the character set of the container database in which the pluggable database resides. The character set with which a PDB is encoded, where the character set is different from the character set of the root database of the PDB's container database, is referred to herein as a “differing” character set. When a PDB is encoded using a differing character set, the character data of the PDB must be fully converted to encoding with the database character set of the container database.
However, it is a costly process to convert the character set encoding of a pluggable database, and there are, at times, reasons why a client would not wish to convert the encoding of a pluggable database. For example, since data conversion can result in data loss or data truncation, converted data is generally reviewed by an administrator, and any such errors must be rectified by the administrator to ensure that the data is free of such errors.
It is possible to perform joint queries over multiple databases that are encoded using different character sets. To perform such a joint query, a user may establish a database link between distinct databases that are encoded with different character sets. Such a link allows the user to access data from a remote database, e.g., as a source from within another database. The data retrieved from the remote database is converted to being encoded based on the character set of the database on which the query is run. Thus, a query can execute over data from multiple distinct databases and return a result set that is encoded using a single character set.
However, processing data over a database link requires user configuration of the database link. Further, performing a distributed query over multiple databases using a database link is less efficient than processing a query over a single database, such as over a consolidated database. Additionally, there are limitations as to what data types can be queried over a database link, which further reduces the utility of such database links.
As such, it would be beneficial to accommodate and query pluggable databases, within a container database, that are encoded with differing character sets. Also, it would be beneficial to be able to perform a query over multiple containers (where each container is encoded with a different character set) without requiring database links to connect the containers.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.